<?php

namespace app\shop\logic;

use think\facade\Db;
use app\data\util\HttpUtil;
use ba\Exception;
use think\facade\Cache;
use app\common\library\Excel;

class DailyEstimateLogic
{
    protected $model = null;

    public function __construct()
    {

    }
    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 列表
     * @author zhangheng
     */
    public function index($params = [])
    {
        $shop_id     = $params['shop_id'] ?? "";
        $day_changes = $params['day_changes'] ?? 0;
        $createtime  = $params['createtime'] ?? '';
        $limit = $params['limit'] ?? 10;
        $where = [];
        if (!empty($shop_id)) {
            $where[] = ['a.shop_id', '=', $shop_id];
        }
        
        switch ($day_changes) {
            case '0':
                $start_time = strtotime(date('Y-m-d 00:00:00', time()-16*3600));
                $end_time = strtotime(date('Y-m-d 23:59:59', time()-16*3600));
                break;
            case '1':
                $start_time = strtotime(date('Y-m-d 00:00:00', time()-16*3600-24*3600));
                $end_time = strtotime(date('Y-m-d 23:59:59', time()-16*3600-24*3600));
                break;
            case '2':
                $start_time = strtotime(date('Y-m-d 00:00:00', time()-16*3600-7*24*3600));
                $end_time = strtotime(date('Y-m-d 23:59:59', time()-16*3600));
                break;
            default:
                $start_time = strtotime(date('Y-m-d 00:00:00', time()-16*3600));
                $end_time = strtotime(date('Y-m-d 23:59:59', time()-16*3600));
        }
        if (!empty($createtime)) {
            $createtimeArr = explode(',', $createtime);
            $start_time = strtotime($createtimeArr[0]);
            $end_time = strtotime($createtimeArr[1]) + 24*3600 - 1;
        }
        $where[] = ['a.createtime', '>=', $start_time];
        $where[] = ['a.createtime', '<=', $end_time];
        //毛利月度统计
        $data = $this->DaylyGrossProfitStatistics($start_time,$end_time,$limit,$where);

        return json(DefaultResponse(200, true, '获取成功', $data));
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 导出
     * @author zhangheng
     */
    public function DaylyGrossProfitStatistics($start_time,$end_time,$limit,$where)
    {

        $list = Db::name('order')
            ->field('
                COUNT(a.id) as count,
                a.shop_id,
                sum(total_amount) as total_amount,
                sum(settlement_amount) as total_settlement_amount,
                sum(shipping_fee_platform_discount) as shipping_fee_platform_discount,
                sum(platform_discount) as platform_discount,
                sum(original_total_product_price) as original_total_product_price,
                sum(tax) as tax,
                b.name as shop_name,
                sum(shipping_fee) as shipping_fee,
                SUM(c.nums) as nums
            ')
            ->alias('a')
            ->join('ba_shops b', 'b.id = a.shop_id', 'left')
            ->join("(SELECT order_id, SUM(num) as nums FROM ba_order_info GROUP BY order_id) c", 'c.order_id = a.order_id', 'left')
            ->where($where)
            ->where('a.status', '<>', 'CANCELLED')
            ->group('a.shop_id')
            ->order('total_amount desc')
            ->paginate($limit);
        $lists = $list->items();
        //计算其他数据
        $exchange = Cache::store('redis')->get('ba_exchange');
        if(empty($exchange)){
            $exchange = Db::name('exchange')
                ->order('date desc')
                ->find();
            Cache::store('redis')->set('ba_exchange', $exchange, 3600);
        }
        $sum['PanelSalesRevenue'] = 0;
        $sum['advertising_spend'] = 0;
        $sum['refund_subtotal'] = 0;
        if(!empty($lists)){
            //面板广告费
            $where_campaign = [];
            $where_campaign[] = ['a.stat_time', '>=', $start_time];
            $where_campaign[] = ['a.stat_time', '<=', $end_time];
            $where_campaign[] = ['a.spend', '>=', 0.001];
            foreach ($lists as $k=>$v) {
                //海外仓运费
                $lists[$k]['storage_fees'] = number_format((float) $v['count'] * $exchange['storage_fees'], 2, '.', '');

                //头程
                $lists[$k]['first_fees'] = number_format((float) $v['count'] * $exchange['first_fees'] / $exchange['proportion'], 2, '.', '');

                //采购成本
                $whereProcurementCost = [];
                $whereProcurementCost[] = ['b.createtime', '>=', $start_time];
                $whereProcurementCost[] = ['b.createtime', '<=', $end_time];
                $ProcurementCostTotalArr = $this->ProcurementCost($whereProcurementCost,$v['shop_id']);
                $lists[$k]['ProcurementCostTotal'] = number_format($ProcurementCostTotalArr['ProcurementCostTotal'] / $exchange['proportion'], 2, '.', '');

                // 退款
                //订单销售额*7.5%
                $refund_subtotal = number_format((float)$v['total_amount'] * $exchange['refund_fee'] / 100, 2, '.', '');
                $lists[$k]['refund_subtotal'] = $refund_subtotal;
                //预估结算费
                $EstimatedSettlementFee = number_format(((float)$v['total_amount'] + $v['platform_discount'] - $v['tax']) * $exchange['platform_fee'] / 100, 2, '.', '');
                $lists[$k]['EstimatedSettlementFee'] = $EstimatedSettlementFee;

                //面板销售额---不减退款 孙总说的
                $PanelSalesRevenue = number_format((float)$v['total_amount'] + $v['platform_discount'] + $v['shipping_fee_platform_discount'] - $v['tax'], 2, '.', '');
                $lists[$k]['PanelSalesRevenue'] = $PanelSalesRevenue;

                //面板广告费
                $advertising_spend = $this->advertising_campaign($where_campaign,$v['shop_id']);
                $lists[$k]['advertising_spend'] = $advertising_spend;
                $lists[$k]['total_settlement_amount'] = getRes($v['total_settlement_amount']);

                //ROI
                if(!empty($lists[$k]['PanelSalesRevenue']) && !empty($lists[$k]['advertising_spend'])){
                    $lists[$k]['roi'] = number_format((float)$lists[$k]['PanelSalesRevenue'] / $lists[$k]['advertising_spend'], 2, '.', '');
                }else{
                    $lists[$k]['roi'] = 0;
                }
                //实际盈利
                $lists[$k]['ActualProfit'] = number_format((float)$lists[$k]['PanelSalesRevenue'] - $lists[$k]['advertising_spend'] - $lists[$k]['storage_fees'] - $lists[$k]['first_fees'] - $lists[$k]['ProcurementCostTotal'], 2, '.', '');

            }
        }

        $data = [
            'list' => $lists,
            'total' => $list->total(),
        ];
        return $data;
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 导出
     * @author zhangheng
     */
    public function export($params = [])
    {
        $lists = [];
        $header[] = ['广告账户', 'advertiser_id'];
        $header[] = ['广告名称', 'advertiser_name'];
        $header[] = ['花费', 'spend'];
        $header[] = ['统计月份', 'month'];
        $fileName = "export_DailyEstimate" . time();
        return Excel::exportData($lists, $header, $fileName, 'xlsx');
    }
    
    /**
    * 采购成本
    * @param array params
    */
    public function ProcurementCost($where,$shop_id)
    {      
        $subQuery = Db::name('product_month_cost')    
            ->field('sku, MAX(date) as max_date')    
            ->group('sku')    
            ->buildSql();    
        $ProcurementCost = Db::name('order_info')    
            ->field('a.spu, SUM(a.num) as num, c.cost')    
            ->alias('a')    
            ->join('ba_order b', 'b.order_id = a.order_id', 'left')    
            ->join([$subQuery => 'sub_c'], 'sub_c.sku = a.spu', 'left')    
            ->join('product_month_cost c', 'c.sku = a.spu AND c.date = sub_c.max_date', 'left')    
            ->where($where)    
            ->where('b.status', '<>', 'CANCELLED')    
            ->where("b.shop_id", $shop_id)    
            ->group("a.spu")    
            ->order('num desc, c.date desc')    
            ->select()->toArray();
        $ProcurementCostTotal = 0;  
        $nums = 0;  
        if(!empty($ProcurementCost)){
            foreach ($ProcurementCost as $item) {  
                $num = (float)$item['num']; // 将num转换为浮点数进行计算  
                $cost = (float)$item['cost']; // 将cost转换为浮点数进行计算  
                $ProcurementCostTotal += number_format($num * $cost, 2, '.', '');
                $nums += $num;
            }
        }
        $ProcurementCostTotalArr['ProcurementCostTotal'] = $ProcurementCostTotal;
        $ProcurementCostTotalArr['nums'] = $nums;
        return $ProcurementCostTotalArr;
    }
    
    /**
    * 方法详情
    * @param array params
    */
    public function advertising_campaign($where_campaign,$shop_id)
    {
        $advertiser = Db::name('advertising_campaign')
            ->field('sum(a.spend) as spend')
            ->alias('a')
            ->join('ba_advaccount b', 'b.advertiser_id = a.advertiser_id', 'left')
            ->join('ba_advaccount_admin c', '
                c.advertiser_id = b.id AND 
                c.createtime <= a.stat_time AND 
                c.updatetime >= a.stat_time AND 
                c.shop_id = '.$shop_id.' AND 
                c.status = "hidden"', 'left')
            ->join('ba_advaccount_admin c2', '
                c2.advertiser_id = b.id AND 
                c2.createtime <= a.stat_time AND 
                c2.shop_id = '.$shop_id.' AND 
                c2.status = "normal"', 'left')
            ->where($where_campaign)
            ->where(function ($query) { 
                $query->where('COALESCE(c.shop_id, c2.shop_id) IS NOT NULL');
            })
            ->order("spend desc")
            ->find();
        $advertising_spend = 0;
        if (!empty($advertiser['spend'])) {
            $advertising_spend = number_format((float)$advertiser['spend'], 2, '.', '');
        }
        return $advertising_spend;
    }
    
}
